-- Tags: long

set optimize_read_in_order=1;
set enable_json_type=1;
set allow_experimental_variant_type=1;
set use_variant_as_common_type=1;
set enable_analyzer=1;

drop table if exists test;

{% for create_command in ['create table test (id UInt64, json JSON(a UInt32), data String) engine=MergeTree order by (json.a, json.b::String) settings min_rows_for_wide_part=100000000, min_bytes_for_wide_part=1000000000, index_granularity=1;',
                          'create table test (id UInt64, json JSON(a UInt32), data String) engine=MergeTree order by (json.a, json.b::String) settings min_rows_for_wide_part=1, min_bytes_for_wide_part=1, vertical_merge_algorithm_min_columns_to_activate=10, vertical_merge_algorithm_min_rows_to_activate=1000000, index_granularity=1;',
                          'create table test (id UInt64, json JSON(a UInt32), data String) engine=MergeTree order by (json.a, json.b::String) settings min_rows_for_wide_part=1, min_bytes_for_wide_part=1, vertical_merge_algorithm_min_columns_to_activate=1, vertical_merge_algorithm_min_rows_to_activate=1, index_granularity=1;'] -%}

select '{{ create_command }}';

{{ create_command }}

system stop merges test;
insert into test select number, toJSONString(map('a', number % 2, 'b', 'str_' || number % 3, 'c', range(number % 10))), 'Hello, World!' from numbers(4);
insert into test select number, toJSONString(map('a', number % 2 + 4, 'b', 'str_' || number % 3 + 4, 'c', range(number % 10))), 'Hello, World!' from numbers(4, 4);

select * from test order by json.a, id;
select trimLeft(explain) from (explain sorting=1 select * from test order by json.a);
select * from test order by json.a desc, id;
select trimLeft(explain) from (explain sorting=1 select * from test order by json.a desc);
select * from test order by json.a, json.b::String, id;
select trimLeft(explain) from (explain sorting=1 select * from test order by json.a, json.b::String);
select * from test order by json.a desc, json.b::String, id;
select trimLeft(explain) from (explain sorting=1 select * from test order by json.a desc, json.b::String);
select * from test order by json.a, json.b::String desc, id;
select trimLeft(explain) from (explain sorting=1 select * from test order by json.a, json.b::String desc);
select * from test order by json.a desc, json.b::String desc, id;
select trimLeft(explain) from (explain sorting=1 select * from test order by json.a desc, json.b::String desc);

select * from test where json.a = 0 order by id;
select trimLeft(explain) from (explain indexes=1 select * from test where json.a = 0);
select * from test where json.a = 0 and json.b::String = 'str_0' order by id;
select trimLeft(explain) from (explain indexes=1 select * from test where json.a = 0 and json.b::String = 'str_0');

select count() from test where json.a = 0 settings optimize_use_implicit_projections = 1;
select trimLeft(explain) from (explain indexes=1 select count() from test where json.a = 0 settings optimize_use_implicit_projections = 1);
select count() from test where json.a = 0 and json.b = 'str_0' settings optimize_use_implicit_projections = 1;
select trimLeft(explain) from (explain indexes=1 select count() from test where json.a = 0 and json.b = 'str_0' settings optimize_use_implicit_projections = 1);

system start merges test;
optimize table test final;
select count() from system.parts where active and table = 'test' and database = currentDatabase();

select * from test order by json.a, id;
select trimLeft(explain) from (explain sorting=1 select * from test order by json.a);
select * from test order by json.a desc, id;
select trimLeft(explain) from (explain sorting=1 select * from test order by json.a desc);
select * from test order by json.a, json.b::String, id;
select trimLeft(explain) from (explain sorting=1 select * from test order by json.a, json.b::String);
select * from test order by json.a desc, json.b::String, id;
select trimLeft(explain) from (explain sorting=1 select * from test order by json.a desc, json.b::String);
select * from test order by json.a, json.b::String desc, id;
select trimLeft(explain) from (explain sorting=1 select * from test order by json.a, json.b::String desc);
select * from test order by json.a desc, json.b::String desc, id;
select trimLeft(explain) from (explain sorting=1 select * from test order by json.a desc, json.b::String desc);

select count() from test where json.a = 0 settings optimize_use_implicit_projections = 1;
select trimLeft(explain) from (explain indexes=1 select count() from test where json.a = 0 settings optimize_use_implicit_projections = 1);
select count() from test where json.a = 0 and json.b = 'str_0' settings optimize_use_implicit_projections = 1;
select trimLeft(explain) from (explain indexes=1 select count() from test where json.a = 0 and json.b = 'str_0' settings optimize_use_implicit_projections = 1);

drop table test;

{% endfor -%}
